/* $Id: tables.sql 17 2008-07-07 07:08:03Z Rook.Alex $
 * koi8-r
 */

/************************
 * BackUp old data
 ***********************/
 drop table statold;
create table statold as select * from statcollector;
/**********************************************
	Avtorizaion & Authentification tables 
***********************************************/
/* billing users 
 *
 */
 /*
drop table if exists account cascade; 
create table account (id_acct varchar(50),
				 first_name varchar(50),
				 second_name varchar(50),
				 last_name varchar(50));

alter table account add constraint pk_account primary key (id_acct) ;
*/

/*
 * users & groups (history support)
 */

drop table if exists entities cascade;

create table entities (id serial,
 		       ename varchar(25),
		       eflag char(1) default 'u' check(eflag ='g' or eflag ='u'),
		       enabled char(1) default 'y' check (enabled='y' or enabled='n')
		       );

alter table entities add constraint pk_entities primary key (id);		       		       

drop table if exists entities_h cascade;
create table entities_h (like entities,
				    id_user_c integer,
			            d time_point,
			             op char(1)
			  	) ;

create or replace rule rhu_entities as on update to entities 
	do also insert into entities_h (id,ename,eflag,enabled,id_user_c,d,op) values(old.id,old.ename,old.eflag,old.enabled,1,now_time_point(),'u');

create or replace rule rhd_entities as on delete to entities
	do also insert into entities_h (id,ename,eflag,enabled,id_user_c,d,op) values(old.id,old.ename,old.eflag,old.enabled,1,now_time_point(),'d');
	
create or replace view vhentities as
select id,ename,eflag,enabled,id_user_c,
       d,op from entities_h
union
select id,ename,eflag,enabled,1,
       null_time_point() as d,'i' from entities;

/*
 * User->Group table with history support (vhusergroup) 
 */
drop table if exists usergroup cascade;
create table usergroup (
                                       id_u int,
				     id_g int,
				     glevel int   -- group level
				     );

drop table if exists usergroup_h cascade;
create table usergroup_h (like usergroup,
				        id_user_c int,
				        d time_point,
				        op char(1)
					) ;
alter table usergroup 
		add constraint 
			pkg_usergroup primary key (id_u,id_g,glevel);

alter table usergroup add constraint fk_usergroup_u foreign key (id_u) references entities on delete cascade;
alter table usergroup add constraint fk_usergroup_g foreign key (id_g) references entities on delete cascade;
    

create or replace rule rhi_usergroup as on update to usergroup
	do  also insert into usergroup_h (id_u,id_g,glevel,id_user_c,d,op)
			values(old.id_u,old.id_g,old.glevel,1,now_time_point(),'u');
		  

create or replace rule rhd_usergroup as on delete to usergroup
	do  also insert into usergroup_h (id_u,id_g,glevel,id_user_c,d,op)
			values(old.id_u,old.id_g,old.glevel,1,now_time_point(),'d'); 
			
create or replace view vhusergroup as 
select id_u,id_g,glevel,d,op from usergroup_h
union 
select id_u,id_g,glevel,null_time_point() as d,'i' from usergroup;
		  

/*
  *	tarifs (history support)
  */
  
drop table if exists tarifs cascade;
create table tarifs (id serial,
                               tarif ttarif);

drop table if exists tarifs_h cascade;			       
create table tarifs_h (like tarifs,
				id_user_c int,
				d time_point,
				op char(1)
				);
				
alter table tarifs add constraint pk_tarifs primary key (id);

create or replace rule rhu_tarifs as on update to tarifs 
	do also insert into tarifs_h (id,tarif,id_user_c,d,op) values (old.id,old.tarif,1,now_time_point(),'u');

create or replace rule rhd_tarifs as on delete to tarifs 
	do also insert into tarifs_h (id,tarif,id_user_c,d,op) values (old.id,old.tarif,1,now_time_point(),'u');
	
create or replace view vhtarifs as
select id,tarif,d,op from tarifs_h
union
select id,tarif,null_time_point() as d,'i' from tarifs;
	  
/*
 * Money register
 */
drop table if exists moneyreg cascade;
create table moneyreg (id_entity integer, -- account ID
				    amount money, -- account deposit
				    depositdate timestamp with time zone --deposit date
				   );

drop table if exists moneyreg_h cascade;				   
create table moneyreg_h (like moneyreg,
				     id_user_c int,
				     d time_point,
				     op char(1)
				     );

alter table moneyreg add 
	constraint fk_user_moneyreg foreign key (id_entity) references entities on delete cascade;				     

	
create or replace rule rhi_moneyreg as on insert to moneyreg 
	do instead insert into moneyreg (id_entity,amount,depositdate) 
 			    values(new.id_entity,new.amount,now()); --for report reasons save depositdate
			
create or replace rule rhu_moneyreg as on update to moneyreg 
	 do also insert into moneyreg_h (id_entity,amount,depositdate,id_user_c,d,op) 
 			values(old.id_entity,old.amount,old.depositdate,1,now_time_point(),'u');
			
create or replace rule rhd_moneyreg as on update to moneyreg 
	 do also insert into moneyreg_h (id_entity,amount,depositdate,id_user_c,d,op) 
 			values(old.id_entity,old.amount,old.depositdate,1,now_time_point(),'d');


create or replace view vhmoneyreg as
	select id_entity,amount,depositdate,d,op from moneyreg_h
		union
	select id_entity,amount,depositdate,null_time_point() as d,'i' from moneyreg;
			
	  
/* operators for AV i.e '=',':=' etc */
drop table if exists avoperator  cascade;
create table avoperator (op varchar(2));
alter table avoperator add constraint pk_avoperator primary key (op);

/* attributes for AV i.e 'Password', 'Framed-Ip-Address' */ 
drop table if exists  attributes cascade;
create table attributes (id_attr int,attribute varchar(33),description varchar(50));
alter table attributes add constraint pk_attributes primary key (id_attr);
alter table attributes add constraint unq_attributes unique (attribute);

/*
 * Values for Radius Attributes
 */
drop table if exists attributevalues cascade;
create table attributevalues (id_value bigserial PRIMARY KEY,
					   id_entity int,
					   id_attr int,
			      		   op varchar(2) not null,
			      		   attr_value varchar(100),
			                    flag char(1) not null default 'c' check (flag='c' or flag='r')
					    );

alter table attributevalues add constraint 
	fk_attributevalues_users foreign key (id_entity) references entities;
alter table attributevalues add  constraint 
	fk_attributevalues_avoperator foreign key (op) references avoperator;
alter table attributevalues add constraint 
	fk_attributevalues_attributes foreign key (id_attr) references attributes;
	

  
/******************************************************
	Accounting tables
*******************************************************/

drop table if exists statcollector cascade;
create table statcollector (
	RadAcctId		BIGSERIAL PRIMARY KEY,
	AcctSessionId		       VARCHAR(32) NOT NULL,
	AcctUniqueId		       VARCHAR(32) NOT NULL,
	UserName		               VARCHAR(253),
--        id_user			       int,
	GroupName		      VARCHAR(253),
	Realm		               VARCHAR(64),
	NASIPAddress		      INET NOT NULL,
	NASPortId		              VARCHAR(15),
	NASPortType		      VaRCHAR(32),
	AcctStartTime		      TIMESTAMP with time zone,
	AcctStopTime		      TIMESTAMP with time zone,
	AcctSessionTime	       BIGINT,
	AcctAuthentic		      VARCHAR(32),
	ConnectInfo_start	      VARCHAR(50),
	ConnectInfo_stop	     VARCHAR(50),
	AcctInputOctets		     BIGINT,
	AcctOutputOctets	     BIGINT,
	CalledStationId		     VARCHAR(50),
	CallingStationId	             VARCHAR(50),
	AcctTerminateCause	    VARCHAR(32),
	ServiceType		     VARCHAR(32),
	XAscendSessionSvrKey   VARCHAR(10),
	FramedProtocol		    VARCHAR(32),
	FramedIPAddress	    INET,
	AcctStartDelay		   INTEGER,
	AcctStopDelay		   INTEGER	
);
/* load old data */
 insert into statcollector
	 select * from statold;
	 
	

create table novalidsession (d time_point,
					  like statcollector);
	 
create or replace function validatesessions() returns void as 
$$
begin
insert into novalidsession select now_time_point(),a.*
from statcollector a where acctsessionid in (
  		select acctsessionid from statcollector b
			where acctstoptime is null and acctstarttime<(
			select max(acctstarttime) from statcollector c where b.username=c.username)
			);
			
delete from statcollector a where acctsessionid in (
  		select acctsessionid from statcollector b
			where acctstoptime is null and acctstarttime<(
			select max(acctstarttime) from statcollector c where b.username=c.username)
			);			
			
 end;
$$ language plpgsql;
	 

select username,costbytes() as sessbytescost,sesstimecost

	 
 
 
 				      

-- This index may be usefull..
-- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress);

-- For use by onoff-, update-, stop- and simul_* queries
--CREATE INDEX radacct_active_user_idx ON radacct (UserName, NASIPAddress, AcctSessionId) WHERE AcctStopTime IS NULL;
-- and for common statistic queries:
--CREATE INDEX radacct_start_user_idx ON radacct (AcctStartTime, UserName);
-- and, optionally
-- CREATE INDEX radacct_stop_user_idx ON radacct (acctStopTime, UserName);

drop table if exists radpostauth cascade;

CREATE TABLE radpostauth (
	id			BIGSERIAL PRIMARY KEY,
	username		VARCHAR(253) NOT NULL,
	pass			VARCHAR(128),
	reply			VARCHAR(32),
	CalledStationId		VARCHAR(50),
	CallingStationId	VARCHAR(50),
	authdate		TIMESTAMP with time zone NOT NULL default 'now'
);


			      
